{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "csv_file = './lyrics_data_Jay.csv'\n",
    "df = pd.read_csv(csv_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to generate random date and time between two dates\n",
    "def random_date(start, end):\n",
    "    \"\"\"Generate a random datetime between `start` and `end`\"\"\"\n",
    "    return start + timedelta(\n",
    "        seconds=random.randint(0, int((end - start).total_seconds())),)\n",
    "\n",
    "\n",
    "# Start and end dates for random creation date\n",
    "start_date = datetime(2023, 1, 1, 0, 0, 0)\n",
    "end_date = datetime(2023, 12, 31, 23, 59, 59)\n",
    "\n",
    "# Generate SQL insert statements\n",
    "insert_statements = []\n",
    "for index, row in df.iterrows():\n",
    "    id = index + 1\n",
    "    music_name = row['歌名']\n",
    "    artist = \"周杰伦\"\n",
    "    music_file = f\"{music_name}.mp3\"\n",
    "    play_count = random.randint(1, 100)\n",
    "    created_at = random_date(\n",
    "        start_date, end_date).strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "    genre_id = random.randint(1, 10)\n",
    "    lyrics = row['歌词']\n",
    "    cover_image = f\"{index + 1}.jpg\"\n",
    "\n",
    "    sql = f\"INSERT INTO music_musicdetails (id, music_name, artist, music_file, play_count, created_at, genre_id, lyrics, cover_image) VALUES ({id}, '{music_name}', '{artist}', '{music_file}', {play_count}, '{created_at}', {genre_id}, '{lyrics}', '{cover_image}');\"\n",
    "    insert_statements.append(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the generated SQL insert statements to a file\n",
    "sql_file_path = './data_Jay.sql'\n",
    "\n",
    "with open(sql_file_path, 'w') as file:\n",
    "    for statement in insert_statements:\n",
    "        file.write(statement + \"\\n\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
